package edu.mit.db.rstore.impl;

import java.sql.SQLException;
import java.util.*;

import edu.mit.db.rstore.*;

import com.hp.hpl.jena.query.Query;
import com.hp.hpl.jena.query.QueryExecution;
import com.hp.hpl.jena.query.QueryExecutionFactory;
import com.hp.hpl.jena.query.QueryFactory;
import com.hp.hpl.jena.query.ResultSet;
import com.hp.hpl.jena.query.ResultSetFormatter;
import com.hp.hpl.jena.rdf.model.Model;
import com.hp.hpl.jena.rdf.model.NsIterator;
import com.hp.hpl.jena.rdf.model.Statement;
import com.hp.hpl.jena.rdf.model.StmtIterator;


/**
 * This class populates the schema generated by the {@link RDFSBasedSchemaGenerator} from the {@link Store}
 * 
 * First it iterates over the tables generated, and based on the primary key(s), gets the relevant attributes
 * through SPARQL queries over the RDF Store
 * 
 * @author oshani
 *
 */
public class RDFSBasedDBPopulator implements DBPopulator 
{
	//Needs at least the schemas from the schema generator and the RDFStore to get the triples from
	private LinkedList<PropertyTable> schemas;
	private RDFStore store;
	
	public RDFSBasedDBPopulator(LinkedList<PropertyTable> db_schemas, RDFStore rdfstore) throws ClassNotFoundException, SQLException
	{
		schemas = db_schemas;
		store = rdfstore;
		
		createTables();
		insertValues();
//		dropEmptyTables();
		
	}
	
	enum Type{
		ONE_TO_ONE,
		ONE_TO_MANY,
		MANY_TO_MANY
	}
	
	/* (non-Javadoc)
	 * @see edu.mit.db.rstore.impl.DBPopulaterInt#createTables()
	 */
	
	public void createTables() throws ClassNotFoundException, SQLException{
		
		DBConnection dbConnection = new DBConnection();
		dbConnection.connect();
		
		for (PropertyTable p: this.schemas){
			
			String tableName = p.table_name;
			
			//First Check if the table exists in the Database
			//If it exists drop the table
			if (dbConnection.tableExists(tableName)){
				dbConnection.st.execute("DROP TABLE "+ tableName +" CASCADE ");
			}
			
			//Then create the new table
			
			String createTableStatement = "";
			if (p instanceof ManyToManyTable) {
				createTableStatement = ((ManyToManyTable) p).getSQL();				
			}
			else if (p instanceof OneToManyTable){
				createTableStatement = ((OneToManyTable) p).getSQL();	
			}
			else{
				createTableStatement = p.getSQL() ;				
			}
			dbConnection.st.execute(createTableStatement);
		}
		dbConnection.close();
	}
	
	/* (non-Javadoc)
	 * @see edu.mit.db.rstore.impl.DBPopulaterInt#insertValues()
	 */
	public void insertValues() throws ClassNotFoundException, SQLException{

		DBConnection dbConnection = new DBConnection();
		dbConnection.connect();

		for (PropertyTable p: this.schemas){
			if (p instanceof ManyToManyTable){

				ManyToManyTable m = (ManyToManyTable)p;
				
				LinkedList<String> pkeyVals = m.getPrimaryKeys();

				//As per the current setting there can only be 2 columns as the primary key
				//So, let's just iterate over subjects corresponding to the first primary key 
				//and find the values that go in the other column of the primary key

				String pkeyVal1 = pkeyVals.getFirst();
				String pkeyVal1LocalName = pkeyVal1.substring(pkeyVal1.indexOf('#')+1);
				
				HashSet<String> pkeys1 = store.getQualifiedSubjectsFromType(pkeyVal1LocalName);

				for (String s: pkeys1){
					String insertStatement = " INSERT INTO "+p.table_name+ " VALUES ( '" + s + "' , '";
					String pkeyVal2 = getOneToOneAttributeValue(m.getPredicate(), s);
					insertStatement += pkeyVal2 + "' , '";

					//Now take care of the columns if there are any
					HashMap<String, String> cols = m.columns;
					Iterator<String> i = cols.keySet().iterator();
					while (i.hasNext()){
						String colVal = (String)i.next();
						String colName = cols.get(colVal);
						String attrVal = getOneToOneAttributeValue(colVal, s);
						if (attrVal != null){
							insertStatement += attrVal + "' , '";
						}
						else{
							insertStatement += "null" + "' , '";
						}
					}
					//Strip off the final ',' and add the ')'
					insertStatement = insertStatement.substring(0, insertStatement.lastIndexOf(','));
					insertStatement += ")";
					dbConnection.st.execute(insertStatement);
					
				}
			}
			else if (p instanceof OneToManyTable) {

				OneToManyTable o = (OneToManyTable) p;

				LinkedList<String> pkeyVals = o.getPrimaryKeys();

				//As per the current setting there can only be 2 columns as the primary key
				//So, let's just iterate over subjects corresponding to the first primary key 
				//and find the values that go in the other column of the primary key

				String pkeyVal1 = pkeyVals.getFirst();
				String pkeyVal1LocalName = pkeyVal1.substring(pkeyVal1.indexOf('#')+1);
				
				HashSet<String> pkeys1 = store.getQualifiedSubjectsFromType(pkeyVal1LocalName);

				for (String s: pkeys1){
					LinkedList<String> pkey2Vals = getOneToManyAttributeValues(o.getPredicate(), s);
					for (int j=0; j<pkey2Vals.size(); j++){
						String insertStatement = " INSERT INTO "+p.table_name+ " VALUES ( '" 
							+ s + "' , '"+pkey2Vals.get(j) + " , ";
						//Now take care of the columns if there are any
						HashMap<String, String> cols = o.columns;
						Iterator<String> i = cols.keySet().iterator();
						while (i.hasNext()){
							String colVal = (String)i.next();
							String colName = cols.get(colVal);
							String attrVal = getOneToOneAttributeValue(colVal, s);
							if (attrVal != null){
								insertStatement += attrVal + "' , '";
							}
							else{
								insertStatement += "null" + "' , '";
							}
						}
						//Strip off the final ',' and add the ')'
						insertStatement = insertStatement.substring(0, insertStatement.lastIndexOf(','));
						insertStatement += "')";
						dbConnection.st.execute(insertStatement);
						
//						System.out.println(o.table_name+"  "+ s +" "+insertStatement);
					}
				}		
			}
			else{
				String pkeyCol = p.getPrimaryKeyColumn();
				String pkeyVal = p.getPrimaryKey();
				String pkeyValLocalName = pkeyVal.substring(pkeyVal.indexOf('#')+1);
				HashSet<String> pkeys = store.getQualifiedSubjectsFromType(pkeyValLocalName);
				for (String s: pkeys){
					//For each of these primary keys Query over the RDF store for each of the columns
					String insertStatement = " INSERT INTO "+p.table_name+ " VALUES ( '" + s + "' , '";
					HashMap<String, String> cols = p.columns;
					Iterator<String> i = cols.keySet().iterator();
					while (i.hasNext()){
						String colVal = (String)i.next();
						String colName = cols.get(colVal);
//						System.out.println(s+ "  " + colName+ "   "+ colVal);
						String attrVal = getOneToOneAttributeValue(colVal, s);
						if (attrVal != null){
							insertStatement += attrVal + "' , '";
						}
						else{
							insertStatement += "null" + "' , '";
						}
					}
					//Strip off the final ',' and add the ')'
					insertStatement = insertStatement.substring(0, insertStatement.lastIndexOf(','));
					insertStatement += ")";
//					System.out.println(insertStatement);
					dbConnection.st.execute(insertStatement);
				}
				
			}
		}
		dbConnection.close();
		
	}
	
	public String getOneToOneAttributeValue(String pred, String sub){
		
		//A temporary hack to the problem in the the fully qualified name
		//for the predicate
    	NsIterator i = store.getRDFModel().listNameSpaces();
    	while (i.hasNext()){
    		String newPred = (String)i.next() + pred;
    		String queryString = "";
    		
			queryString = 
    			"SELECT ?variable " +
    			"WHERE {" +
    			"<" +sub + "> <" + newPred + "> "+ " ?variable .}";
        		
    		Query query = QueryFactory.create(queryString);

    		// Execute the query and obtain results
    		QueryExecution qe = QueryExecutionFactory.create(query, store.getRDFModel());
    		ResultSet results = qe.execSelect();

    		Model resultModel =	ResultSetFormatter.toModel(results);
    		
    		
    		StmtIterator iter = resultModel.listStatements();
    		while (iter.hasNext()){
    			Statement statement = iter.nextStatement();
    			//I believe this should be the standard way the final value will be encoded
    			String valueExpected = "http://www.w3.org/2001/sw/DataAccess/tests/result-set#value";
    			if (statement.getPredicate().toString().equals(valueExpected)){
    				return statement.getObject().toString();
    			}
    		}
     		qe.close();

    	}

		return null;
	}

	
	public LinkedList<String> getOneToManyAttributeValues(String pred, String sub){
		
		LinkedList<String> ret = new LinkedList<String>();
		
		//A temporary hack to the problem in the the fully qualified name
		//for the predicate
    	NsIterator i = store.getRDFModel().listNameSpaces();
    	while (i.hasNext()){
    		String newPred = (String)i.next() + pred;
    		String queryString = "";
    		
			queryString = 
				"SELECT ?variable " +
				"WHERE {" +
				"<" +sub + "> <" + newPred + "> "+ " ?b ." +
			    "?b ?x ?variable .}";
        		
    		Query query = QueryFactory.create(queryString);

    		// Execute the query and obtain results
    		QueryExecution qe = QueryExecutionFactory.create(query, store.getRDFModel());
    		ResultSet results = qe.execSelect();

    		Model resultModel =	ResultSetFormatter.toModel(results);
    		
    		
    		StmtIterator iter = resultModel.listStatements();
    		while (iter.hasNext()){
    			Statement statement = iter.nextStatement();
    			//I believe this should be the standard way the final value will be encoded
    			String valueExpected = "http://www.w3.org/2001/sw/DataAccess/tests/result-set#value";
    			//And we do not need Seq in the result set returned
    			String seq = "http://www.w3.org/1999/02/22-rdf-syntax-ns#Seq";
    			if (statement.getPredicate().toString().equals(valueExpected) &&
    				!(statement.getObject().toString().equals(seq))){
    				ret.add(statement.getObject().toString());
    			}
    		}
     		qe.close();
    	}
		return ret;
	}

	
	public void dropEmptyTables() throws ClassNotFoundException, SQLException{

		DBConnection dbConnection = new DBConnection();
		dbConnection.connect();
		
		for (PropertyTable p: this.schemas){
			
			String tableName = p.table_name;
			
			if (dbConnection.tableExists(tableName) && dbConnection.tableHasNoRows(tableName)){
				dbConnection.st.execute("DROP TABLE "+ tableName + " CASCADE ");
			}
		}
		dbConnection.close();
	}

	public void cleanUp() throws ClassNotFoundException, SQLException {
		
		DBConnection dbConnection = new DBConnection();
		dbConnection.connect();
		
		for (PropertyTable p: this.schemas){
			
			String tableName = p.table_name;
			
			if (dbConnection.tableExists(tableName)){
				dbConnection.st.execute("DROP TABLE "+ tableName + " CASCADE ");
			}
		}
		dbConnection.close();
	}

	
}
